const db = require("./DBConnect");
const conn = db.connection;

const pagerHelper = require("../../util/PagerHelper");

/**
 * 自定义查询接口
 * @param sql   查询的sql命令
 * @param callback  结果回调处理
 * @returns {Promise<unknown>}
 */
function toPromise(sql, callback) {
    console.log("正在执行的sql：", sql);
    return new Promise(((resolve, reject) => {
        conn.query(sql, (err, results) => {
            if (err) {
                reject(err);
            }else {
                // console.log("results", results);
                if (callback) callback(results, resolve);
                else resolve(results);
            }
        });
    }))
}

function getInsertSqlFromObj(obj, table) {
    let sql = `insert into ${table}(`;
    for (let key of Object.keys(obj)) {
        sql += key + ",";
    }
    sql = sql.substring(0, sql.length - 1);
    sql += ") values(";
    for (let val of Object.values(obj)) {
        sql += "'" + val + "',";
    }
    sql = sql.substring(0, sql.length - 1);
    sql += ");";
    return sql;
}

function getUpdateSqlFromObj(obj, table) {
    let sql = `update ${table} set `;
    for (let [key, val] of Object.entries(obj)) {
        if (key == "id") continue;
        sql += `${key} = '${val}', `;
    }
    sql = sql.substring(0, sql.lastIndexOf(","));
    sql += " where id = " + "'"+obj.id+"'";
    return sql;
}


/**
 * 分页查询
 * @param table 表名
 * @param where where语句
 * @param columns   字段列表
 * @returns {Promise<unknown>}
 */
function findByPage(table, where, columns) {
    return toPromise(`select ${columns ? columns : "*"} from ${table} ${where} limit ${(pagerHelper.pageNum - 1) * pagerHelper.pageSize}, ${pagerHelper.pageSize}`);
}

/**
 * 非分页查询，所有条件自定义
 * @param table 表名
 * @param where where语句
 * @param columns   字段列表
 * @returns {Promise<unknown>}
 */
function findBy(table, where, columns) {
    return toPromise(`select ${columns ? columns : "*"} from ${table} ${where ? where : ''} `);
}

/**
 * 查询一条数据
 * @param table 表名
 * @param where where语句
 * @param columns   字段列表
 * @returns {Promise<unknown>}
 */
function findOne(table, where, columns) {
    return toPromise(`select ${columns ? columns : "*"} from ${table} ${where}`, (results, resolve) => {
        resolve(results ? results[0] : null);
    });
}

/**
 * 根据id搜索
 * @param id
 * @param table 表名
 * @param columns   字段列表
 * @returns {Promise<unknown>}
 */
function findById(id, table, columns) {
    return toPromise(`select ${columns ? columns : "*"} from ${table} where id = ${id}`, (results, resolve) => {
        resolve(results ? results[0] : null);
    });
}

/**
 * 查询数据总数
 * @param table 表名
 * @param where where语句
 * @returns {Promise<*>}
 */
function getCount(table, where) {
    return toPromise(`select count(1) as count from ${table} ${where}`, (results, resolve) => {
        resolve(results[0].count);
    });
}

/**
 * 根据id删除数据
 * @param table 表名
 * @param id
 * @returns {Promise<unknown>}
 */
function deleteById(table, id) {
    return toPromise(`delete from ${table} where id = ${id}`);
}

/**
 * 删除多个ids的数据
 * @param table 表名
 * @param ids   必须是数组
 * @returns {Promise<*>}
 */
function deleteByIds(table, ids) {
    if (Array.isArray(ids)) {
        let str = "";
        ids.forEach(value => {
            str += "'" + value + "',";
        })
        str = str.substring(0, str.length - 1);
        return toPromise(`delete from ${table} where id in (${str})`);
    }
}

/**
 * 插入数据到数据库中
 * @param table 表名
 * @param obj
 * @returns {Promise<EnforceDocument<unknown, {}>[]>}
 */
function insert(table, obj) {
    return toPromise(getInsertSqlFromObj(obj, table));
}

/**
 * 编辑书本
 * @param table 表名
 * @param obj
 * @returns {Promise<*>}
 */
function update(table, obj) {
    return toPromise(getUpdateSqlFromObj(obj, table));
}

module.exports = {
    findByPage, findById, findOne, getCount, deleteById, deleteByIds, insert, update, toPromise, findBy
}